<?php

class ecrmUpdateTask extends sfBaseTask
{
  protected function configure()
  {
    // // add your own arguments here
    // $this->addArguments(array(
    //   new sfCommandArgument('my_arg', sfCommandArgument::REQUIRED, 'My argument'),
    // ));

    $this->addOptions(array(
      new sfCommandOption('application', null, sfCommandOption::PARAMETER_REQUIRED, 'The application name'),
      new sfCommandOption('env', null, sfCommandOption::PARAMETER_REQUIRED, 'The environment', 'dev'),
      new sfCommandOption('connection', null, sfCommandOption::PARAMETER_REQUIRED, 'The connection name', 'doctrine'),
      new sfCommandOption('db_source_id', 'd', sfCommandOption::PARAMETER_REQUIRED, 'The database source to use'),
      new sfCommandOption('records_per_query', 'r', sfCommandOption::PARAMETER_REQUIRED, 'Amount of records to be fetched per database query',100)
      // add your own options here
    ));

    $this->namespace        = 'ecrm';
    $this->name             = 'update';
    $this->briefDescription = 'Updates the DB';
    $this->detailedDescription = <<<EOF
The [ecrm:update|INFO] task does things.
Call it with:

  [php symfony ecrm:update|INFO]
EOF;
  }


  protected function execute($arguments = array(), $options = array())
  {
  	gc_enable();
  	try {
  		// initialize the database connection
  		$databaseManager = new sfDatabaseManager($this->configuration);
  		$connection = $databaseManager->getDatabase($options['connection'])->getConnection();

  		// add your code here
  		$dbSourceId = $options['db_source_id'];

  		if(!is_null($dbSourceId)) {
  			$dbSource = Doctrine_Core::getTable('DatabaseSource')->findOneById($dbSourceId);
  			if($dbSource) {
  				$this->log($dbSource->getName().' found!');
  				$this->updateOne($dbSource,$options);
  			}
  			else {
  				$this->log('Can\'t find specified database');
  			}
  		}
  		else {
  			$this->log('No DB Source id passed. Will start updating all.');
  			$this->updateAll($options);
  		}

  		// send exception message to email
  		$message = $this->getMailer()->compose(
  		array('ecrm@no-reply.com' => 'eCRM mailer bot'),
  		array('ssantiago@netbooster.com'),
  		  		'eCRM update task finished!',
  					'Finished update of eCRM!'
  		);

  		$this->getMailer()->send($message);

  	}
  	catch(Exception $e) {
  		$this->log($e->getMessage());

  		// send exception message to email
  		$message = $this->getMailer()->compose(
  			array('ecrm@no-reply.com' => 'eCRM mailer bot'),
//   			array('ssantiago@netbooster.com','rzamora@netbooster.com'),
   			array('ssantiago@netbooster.com'),
  			'eCRM update task error',
  			$e->getMessage()
  		);

  		$this->getMailer()->send($message);
  		exit();
  	}

  	gc_disable();

  }


  private function updateAll($options)
  {
  	$databases = Doctrine_Core::getTable('DatabaseSource')->findAll();

  	foreach($databases as $db) {
//   		echo $db->getName()."\n";
  		$manager = Doctrine_Manager::getInstance();
  		$manager->setCurrentConnection('doctrine');
  		// Check if fields are mapped
  		$mappedFields = Doctrine_Core::getTable('FieldMapping')->findByDatabaseSourceId($db->getId());
  		if($mappedFields) {
  			$this->log('Reading '.$db->getName().' database');
  			$this->updateOne($db,$options);
  		}
  		else {
  			$this->log($db->getName().' fields are not yet mapped');
  		}
  		$mappedFields->free(true);
  		unset($mappedFields);
  	}

  }

  private function updateOne(DatabaseSource $dbSource, $options)
  {
  	do {
  		$conn = Doctrine_Manager::connection($dbSource->getDriver().'://'.$dbSource->getUsername().':'.$dbSource->getPassword().'@'.$dbSource->getServer().'/'.$dbSource->getName(), 'connection '.microtime());
  		if($dbSource->getCustomSql() == '' || is_null($dbSource->getCustomSql())) {
  			$sqlQuery = 'SELECT * FROM `'.$dbSource->getTableSource().'` WHERE '.$dbSource->getIdColumn().' > '.$dbSource->getLastId().' ORDER BY '.$dbSource->getIdColumn().' ASC ';
  		}
  		else {
  			if(strpos($dbSource->getCustomSql(), 'WHERE') !== FALSE) {
  				$sqlQuery = $dbSource->getCustomSql().' AND '.$dbSource->getTableSource().'.'.$dbSource->getIdColumn().' > '.$dbSource->getLastId().' ORDER BY '.$dbSource->getTableSource().'.'.$dbSource->getIdColumn().' ASC ';
  			}
  			else {
  				$sqlQuery = $dbSource->getCustomSql().' WHERE '.$dbSource->getTableSource().'.'.$dbSource->getIdColumn().' > '.$dbSource->getLastId().' ORDER BY '.$dbSource->getTableSource().'.'.$dbSource->getIdColumn().' ASC ';
  			}
  		}
  		$sqlQuery.=' LIMIT '.$options['records_per_query'];
  		$this->log('before unset:'.memory_get_usage());
  		$q = $conn->execute($sqlQuery)->fetchAll();
  		$this->log('after fetch:'.memory_get_usage());
  		$rowCount = count($q);

  		if($rowCount > 0) {
  			$this->updateDB($dbSource, $q);
  			$manager = Doctrine_Manager::getInstance();
  			$manager->closeConnection($conn);
  		}
  		else {
  			$this->log('no more records for '.$dbSource->getName());
  		}
//   		$q = null;
  		unset($q);
  		gc_collect_cycles();
  		$this->log('after unset:'.memory_get_usage());

  	} while($rowCount > 0);

  	$this->log($dbSource->getName().' finished!');

	}


  private function updateDB($dbSource, $records)
  {
  	$manager = Doctrine_Manager::getInstance();
  	$manager->setCurrentConnection('doctrine');
  	for($i = 0;$i < count($records); 	$i++) {
  		$row = $records[$i];
  		$personalInfo = new PersonalInformation();
  		$companyInfo = new CompanyInformation();
  		$socialInfo = new SocialInformation();
  		$campaign = new Campaign();
  		$otherInformations = array();

  		foreach($row as $field=>$val) {
	  		$fieldMappingRow = Doctrine_Core::getTable('FieldMapping')->findOneByDatabaseSourceIdAndExternalFieldName($dbSource->getId(),$field);
	  		if($fieldMappingRow) {

	  		//echo $fieldMappingRow->getExternalFieldName().' - '.$val."\r\n";

	  			$fieldNameSplit = explode('|', $fieldMappingRow->getInternalFieldName());


		  		if($fieldNameSplit[0] == 'personal' && ($val != '' && $val != NULL)) {
		  			if($fieldNameSplit[1] == 'city_id') {
		  				$getCityName = Doctrine_Core::getTable('City')->findOneByName($val);
		  				if($getCityName) {
		  					$personalInfo->setCityId($getCityName->getId());
		  				}
		  				unset($getCityName);
		  			}
		  			else if($fieldNameSplit[1] == 'birthdate') {
		  				if($val != null && $val != '') {
		  					$personalInfo->setBirthdate(date('Y-m-d',strtotime($val)));
		  				}
		  			}
		  			else {
		  				$personalInfo->set($fieldNameSplit[1],$val);
		  			}
					}
	  		  else if($fieldNameSplit[0] == 'company' && ($val != '' && $val != NULL)) {
	  		  	if($fieldNameSplit[1] == 'city_id') {
	  		  		$getCityName = Doctrine_Core::getTable('City')->findOneByName($val);
	  		  		if($getCityName) {
	  		  			$companyInfo->setCityId($getCityName->getId());
	  		  		}
	  		  		unset($getCityName);
	  		  	}
	  		  	else {
	  		  		$companyInfo->set($fieldNameSplit[1],$val);
	  		  	}
	  			}
		  		else if($fieldNameSplit[0] == 'social' && ($val != '' && $val != NULL)) {
		  			$socialInfo->set($fieldNameSplit[1],$val);
		  		}
		  		else {
			  		if($fieldNameSplit[1] == 'custom' && ($val != '' && $val != NULL)) {
			  			$otherInformationsIndex = count($otherInformations);
			  			$otherInformations[$otherInformationsIndex] = new OtherInformation();
			  			$otherInformations[$otherInformationsIndex]->setFieldName($fieldMappingRow->getExternalFieldName());
			  			$otherInformations[$otherInformationsIndex]->setFieldValue($val);
			  			unset($otherInformationsIndex);
			  		}
		  		}



	  				//           $personalInfo->set($fieldMappingRow->getInternalFieldName(),$val);
	  			unset($fieldMappingRow,$fieldNameSplit);
	  		}

  		}

  		try {

  			$emailFieldRow = Doctrine_Core::getTable('FieldMapping')->findOneByDatabaseSourceIdAndInternalFieldName($dbSource->getId(),'|email');
  			$emailField = $emailFieldRow->getExternalFieldName();

  			$v = new sfValidatorEmail();
  			$row[$emailField] = $v->clean($row[$emailField]);


  			$personalInfo->save();
  			//$this->log('Saved Personal Info');
  			$companyInfo->save();
  			//$this->log('Saved Company Info');

  			$campaign->setPersonalInformationId($personalInfo->getId());
  			$campaign->setCompanyInformationId($companyInfo->getId());
  			$campaign->setDatabaseSourceId($dbSource->getId());
  			$campaign->setName($dbSource->getCampaignName());


  			$existingRecord = Doctrine_Core::getTable('ContantInformation')->findOneByEmail($row[$emailField]);

  			$registrationDateRow = Doctrine_Core::getTable('FieldMapping')->findOneByDatabaseSourceIdAndInternalFieldName($dbSource->getId(),'|registration_date');
  			if($registrationDateRow) {
  				$registrationDateField = $registrationDateRow->getExternalFieldName();
  				$registrationDate = $row[$registrationDateField];
  			}
  			else {
  				$registrationDate = date('Y-m-d H:i:s');
  			}
  		  //$registrationDateField = $registrationDateRow->getExternalFieldName();
  			//$registrationDate = ($row[$registrationDateField] == NULL || $row[$registrationDateField] == '') ? date('Y-m-d H:i:s') : $row[$registrationDateField];
  			//$registrationDate = $row[$registrationDateField];
  			if($existingRecord) {
  				//$this->log('Existing');
  				$contantInfo = $existingRecord;
  				
  				/*
  				 * If record is from GHP or MDS 
  				 */
          if($dbSource->getCampaignName() == 'GHP' || $dbSource->getCampaignName() == 'MDS') {
          	
          	/* 
          	 * Check if GHP subscriber or MDS subscriber already
	           */
          	if($contantInfo->getGhpSubscriber() == 'Yes' || $contantInfo->getMdsSubscriber() == 'Yes') {
          		$newerRecord = strtotime($row[$registrationDateField]) > strtotime($contantInfo->getLastRegistrationDate());
          	}
          	else {
          		$contantInfo->setContactSource('Globe');
          		$newerRecord = true;
          	}
            
          }
          else {
          	$newerRecord = strtotime($row[$registrationDateField]) > strtotime($existingRecord->getLastRegistrationDate());
          }
          
  			}
  			else {
  				$contantInfo = new ContantInformation();
  				$contantInfo->setRegistrationDate($registrationDate);
  				$contantInfo->setEmail($row[$emailField]);
  				$newerRecord = true;
  			}
  			if($newerRecord === true) {
          if($personalInfo->getFirstName() != '' && $personalInfo->getFirstName() != NULL) {
            $contantInfo->setFirstName($personalInfo->getFirstName());
          }
          if($personalInfo->getLastName() != '' && $personalInfo->getLastName() != NULL) {
            $contantInfo->setLastName($personalInfo->getLastName());
          }
          if($personalInfo->getMiddleName() != '' && $personalInfo->getMiddleName() != NULL) {
            $contantInfo->setMiddleName($personalInfo->getMiddleName());
          }
          if($personalInfo->getContactNo() != '' && $personalInfo->getContactNo() != NULL) {
            $contantInfo->setContactNo($personalInfo->getContactNo());
          }
          if($personalInfo->getMobileNo() != '' && $personalInfo->getMobileNo() != NULL) {
            $contantInfo->setMobileNo($personalInfo->getMobileNo());
          }
          if($personalInfo->getBirthdate() != '' && $personalInfo->getBirthdate() != NULL) {
            $contantInfo->setBirthdate($personalInfo->getBirthdate());
          }
  				if($personalInfo->getCityId() !== NULL && $personalInfo->getCityId() != '') {
  					$city = Doctrine_Core::getTable('City')->findOneById($personalInfo->getCityId());
  					$cityName = $city->getName();
  				}
  				else {
  					$cityName = '';
  				}
          if($personalInfo->getAddress1().' '.$personalInfo->getStreet().' '.$personalInfo->getBarangay().' '.$cityName != '') {
            $contantInfo->setAddress($personalInfo->getAddress1().' '.$personalInfo->getStreet().' '.$personalInfo->getBarangay().' '.$cityName);
          }
          if($personalInfo->getCivilStatus() != '' && $personalInfo->getCivilStatus() != NULL) {
            $contantInfo->setCivilStatus($personalInfo->getCivilStatus());
          }
          if($personalInfo->getCitizenship() != '' && $personalInfo->getCitizenship() != NULL) {
            $contantInfo->setCitizenship($personalInfo->getCitizenship());
          }
          if($companyInfo->getEmploymentStatus() != '' && $companyInfo->getEmploymentStatus() != NULL) {
            $contantInfo->setEmploymentStatus($companyInfo->getEmploymentStatus());
          }
          if($companyInfo->getDesignation() != '' && $companyInfo->getDesignation() != NULL) {
            $contantInfo->setDesignation($companyInfo->getDesignation());
          }
	  			if($contantInfo->getRegistrationDate() == null || $contantInfo->getRegistrationDate() == '') {
	  				$contantInfo->setRegistrationDate($registrationDate);
	  			}
          //if(strtotime($registrationDate) > strtotime($contantInfo->getLastRegistrationDate())) {
            $contantInfo->setLastRegistrationDate($registrationDate);
          //}
          if($personalInfo->getTitle() != '' && $personalInfo->getTitle() != NULL) {
            $contantInfo->setTitle($personalInfo->getTitle());
          }
          if($personalInfo->getKids() != '' && $personalInfo->getKids() != NULL) {
            $contantInfo->setKids($personalInfo->getKids());
          }
          if($dbSource->getCampaignName() == 'GHP') {
            $contantInfo->setGhpSubscriber('Yes');
          }
          if($dbSource->getCampaignName() == 'MDS') {
          	$contantInfo->setMdsSubscriber('Yes');
          }
          $clientIDRow = Doctrine_Core::getTable('FieldMapping')->findOneByDatabaseSourceIdAndInternalFieldName($dbSource->getId(),'|client_id');
          if($clientIDRow) {
            $clientID = $clientIDRow->getExternalFieldName();
            $contantInfo->setClientId($row[$clientID]);
          }
          if($dbSource->getCampaignName() == 'GHP' || $dbSource->getCampaignName() == 'MDS') {
            $contantInfo->setContactSource('Globe');
          }
	  			$contantInfo->save();
	  			//$this->log('Saved Contant Info');

	  			unset($newerRecord);
  			}

  			$campaign->setContantInformationId($contantInfo->getId());
  			$campaign->save();
  			//$this->log('Saved Campaign Info');

  			$socialInfo->setCampaignId($campaign->getId());
  			$socialInfo->save();

  			foreach($otherInformations as $otherInfo) {
  				$otherInfo->setCampaignId($campaign->getId());
  				$otherInfo->save();
  				$otherInfo->free(true);
  				unset($otherInfo);
  			}
  			unset($otherInformations);


  			$personalInfo->free(true);
//   			$personalInfo = null;
  			unset($personalInfo);
  			$companyInfo->free(true);
//   			$companyInfo = null;
  			unset($companyInfo);
  			$socialInfo->free(true);
//   			$socialInfo = null;
  			unset($socialInfo);
  			$campaign->free(true);
//   			$campaign = null;
  			unset($campaign);
  			$contantInfo->free(true);
//   			$contantInfo = null;
  			unset($contantInfo);
  				//$this->log('Saved Other Info');

  			$dbSource->setLastId($row[$dbSource->getIdColumn()]);
  			$dbSource->save();
//   			$row = null;
  			unset($row);
  		}
  		catch(sfValidatorError $v) {
//   			$this->log('Invalid email');
  			$dbSource->setLastId($row[$dbSource->getIdColumn()]);
  			$dbSource->save();
  		}
  		catch(Exception $e) {
  			$this->log($e->getMessage());

  			// send exception message to email
  			$message = $this->getMailer()->compose(
  			array('ecrm@no-reply.com' => 'eCRM mailer bot'),
  			array('ssantiago@netbooster.com','rzamora@netbooster.com'),
  			  			'eCRM update task error',
  			$e->getMessage()
  			);

  			$this->getMailer()->send($message);
  			exit();

  		}

  	}

		unset($records);
  }



}
